<?php
/******************************
 * EQdkp
 * Copyright 2002-2005
 * Licensed under the GNU GPL.  See COPYING for full terms.
 * ------------------
 * upgrade.php
 * Began: Tue July 1 2003
 *
 *
 ******************************/

define('EQDKP_INC', true);
$eqdkp_root_path = './';
include_once($eqdkp_root_path . 'common.php');

//
// Verify user has permission
//
$user->check_auth('a_');

//
// Verify that this is an upgrade, not an install
//
$config_file = $eqdkp_root_path . 'config.php';
if (!file_exists($config_file) ||
    !is_writeable($config_file) ||
    !is_readable($config_file))
{
    redirect('install.php' . $SID);
}

// Requires MySQL version 4.0.4 minimum.
$version = mysql_get_server_info();

if ( ! ($version >= "4.0") )
{

    printf("MySQL server version is not sufficient for EQdkp. ");
    printf("EQdkp and FFdkp require MySQL version > 4.0 - you are running \n");
    printf("version %s .\n", $version);
    die;
}

class Upgrade
{
    var $db = null;
    var $eqdkp_versions = array('1.0.0','1.1.0','1.2.0B1','1.2.0B2','1.2.0RC1','1.2.0RC2','1.2.0','1.3.0','1.3.1');

    var $versions = array('0.0.1', '0.1.1', '0.1.2');

    function upgrade()
    {
        global $db, $eqdkp, $user, $tpl, $pm;
        global $SID;

        $db->error_die(false);

        if ( isset($_POST['upgrade']) )
        {
            // Find out what version we're upgrading from
            $version_from = $_POST['version'];
            foreach ( $this->versions as $index => $version )
            {
                if ( str_replace('.', '', $version) == $version_from )
                {
                    $method = 'upgrade_' . $version_from;
                    $this->$method($index);
                }
            }
        }
        else
        {
            $this->display_form();
        }
    }

    function finalize($index)
    {
        global $user;

        if ( isset($this->versions[$index + 1]) )
        {
            $method = 'upgrade_' . str_replace('.', '', $this->versions[$index + 1]);
            $this->$method($index + 1);
        }
        else
        {
            message_die($user->lang['upgrade_complete'], $user->lang['success']);
        }
    }

    //--------------------------------------------------------------
    // Upgrade methods
    //--------------------------------------------------------------
    function upgrade_001($index)
    {
        global $db, $eqdkp, $user, $tpl, $pm;
        global $SID, $table_prefix;

        $queries = array();

        $queries[] = "CREATE TABLE `".$table_prefix."triggers` (
   `trigger_id` MEDIUMINT( 8 ) UNSIGNED NOT NULL AUTO_INCREMENT,
   `trigger_name` VARCHAR( 255 ) DEFAULT NULL ,
   `trigger_holder` VARCHAR( 50 ) DEFAULT NULL ,
   `raid_gained_id` INT( 10 ) UNSIGNED NOT NULL default '0',
   `raid_used_id` INT( 10 ) UNSIGNED NULL DEFAULT NULL ,
   `trigger_date` INT( 11 ) NOT NULL DEFAULT '0',
   `trigger_added_by` VARCHAR( 30 ) NOT NULL ,
   `trigger_updated_by` VARCHAR( 30 ),
   `trigger_group_key` VARCHAR( 32 ),
   PRIMARY KEY (`trigger_id`)
 )TYPE=InnoDB;";

        foreach ($queries as $sql)
        {
            $db->query($sql);
        }

        $this->finalize($index);
    }
    
    function upgrade_011($index)
    {
        global $db, $eqdkp, $user, $tpl, $pm;
        global $SID, $table_prefix;

        // Build the new views
        $queries = array();

        $queries[] = "CREATE VIEW `".$table_prefix."v_member_data_lifetime` AS
    SELECT m.member_id,
           m.member_name,
           m.member_raidcount AS raid_count,
           m.member_raidcount / count( DISTINCT ra.raid_id ) AS raid_percent
    FROM eqdkp_raids ra, eqdkp_members m
    WHERE ra.raid_date >= m.member_firstraid
    GROUP BY m.member_id;";

        $queries[] = "CREATE VIEW `".$table_prefix."v_member_data_thirty` AS
    SELECT m.member_id AS member_id, 
           m.member_name AS member_name, 
           count( DISTINCT ra.raid_id ) AS raid_count,
           count( DISTINCT ra.raid_id ) / (
                  SELECT count( DISTINCT raid_id )
                  FROM eqdkp_raids
                  WHERE raid_date >= ( unix_timestamp( ) - ( 86400 *30 ) )
                    AND raid_date <= unix_timestamp( ) )
           ) AS raid_percent
    FROM eqdkp_members m
    LEFT JOIN eqdkp_raid_attendees ra ON m.member_name = ra.member_name
    LEFT JOIN eqdkp_raids r ON ra.raid_id = r.raid_id
    WHERE r.raid_date >= ( unix_timestamp( ) - ( 86400 *30 ) )
      AND r.raid_date <= unix_timestamp( )
    GROUP BY m.member_id";

        $queries[] = "CREATE VIEW `".$table_prefix."v_member_data_sixty` AS
    SELECT m.member_id AS member_id, 
           m.member_name AS member_name, 
           count( DISTINCT ra.raid_id ) AS raid_count,
           count( DISTINCT ra.raid_id ) / (
                  SELECT count( DISTINCT raid_id )
                  FROM eqdkp_raids
                  WHERE raid_date >= ( unix_timestamp( ) - ( 86400 *60 ) )
                    AND raid_date <= unix_timestamp( ) )
           ) AS raid_percent
    FROM eqdkp_members m
    LEFT JOIN eqdkp_raid_attendees ra ON m.member_name = ra.member_name
    LEFT JOIN eqdkp_raids r ON ra.raid_id = r.raid_id
    WHERE r.raid_date >= ( unix_timestamp( ) - ( 86400 *60 ) )
      AND r.raid_date <= unix_timestamp( )
    GROUP BY m.member_id";

        $queries[] = "CREATE VIEW `".$table_prefix."v_member_data_ninety` AS
    SELECT m.member_id AS member_id, 
           m.member_name AS member_name, 
           count( DISTINCT ra.raid_id ) AS raid_count,
           count( DISTINCT ra.raid_id ) / (
                  SELECT count( DISTINCT raid_id )
                  FROM eqdkp_raids
                  WHERE raid_date >= ( unix_timestamp( ) - ( 86400 *90 ) )
                    AND raid_date <= unix_timestamp( ) )
           ) AS raid_percent
    FROM eqdkp_members m
    LEFT JOIN eqdkp_raid_attendees ra ON m.member_name = ra.member_name
    LEFT JOIN eqdkp_raids r ON ra.raid_id = r.raid_id
    WHERE r.raid_date >= ( unix_timestamp( ) - ( 86400 *90 ) )
      AND r.raid_date <= unix_timestamp( )
    GROUP BY m.member_id;";

        foreach ($queries as $query)
        {
            $db->query($query);
        }

        unset($queries);

        // build the new member_class table
        $sql = "
        CREATE TABLE IF NOT EXISTS `".$table_prefix."member_classes` (
        `member_class_id` smallint(5) unsigned NOT NULL auto_increment,
        `member_id` smallint(5) unsigned NOT NULL,
        `class_id` bigint(3) unsigned NOT NULL,
        `secondary_class` tinyint(1) NOT NULL default '0',
        PRIMARY KEY  (`member_class_id`)
        ) AUTO_INCREMENT=1 ;";

        $db->query($sql);

        // Next, get the current class ids
        $classes = array();

        $sql = 'SELECT DISTINCT class_id FROM '.$table_prefix.'classes';
        $result = $db->query($sql);

        while ($row = $db->fetch_record($result))
        {
            $classes[] = $row['class_id'];
        }

        foreach ($classes as $class_id)
        {
            $member_ids = array();
            # Update for primary class
            $sql = "SELECT DISTINCT m.member_id FROM ".MEMBERS_TABLE." m
            WHERE (m.member_class_id & ".$class_id.") = '".$class_id."'";

            $result = $db->query($sql);

            while ($row = $db->fetch_record($result))
            {
                $member_ids[] = $row['member_id'];
            }

            foreach ($member_ids as $member_id)
            {
                if ($member_id >= 0)
                {
                    $insert_sql = "INSERT INTO ".$table_prefix."member_classes (member_id, class_id, secondary_class) VALUES ";
                    $insert_sql = $insert_sql."('".$member_id."','".$class_id."','0') ";
                    $db->query($insert_sql);
                }
            }

            unset($member_ids);

            # Update for secondary class
            $sql = "SELECT DISTINCT m.member_id FROM ".MEMBERS_TABLE." m
                WHERE (m.member_second_class_id & ".$class_id.") = '".$class_id."'";

            $result = $db->query($sql);

            while ($row = $db->fetch_record($result))
            {
                $member_ids[] = $row['member_id'];
            }

            if (isset($member_ids))
            {
                foreach ($member_ids as $member_id)
                {
                    if ($member_id >= 0)
                        {
                            $insert_sql = "INSERT INTO ".$table_prefix."member_classes (member_id, class_id, secondary_class) VALUES ";
                            $insert_sql = $insert_sql."('".$member_id."','".$class_id."','1') ";
                            $db->query($insert_sql);
                        }
                }
            }
        }

        $this->finalize($index);
    }

    function upgrade_012($index)
    {
        global $db, $eqdkp, $user, $tpl, $pm;
        global $SID, $table_prefix;

        $queries = array(
                         "CREATE TABLE IF NOT EXISTS ".$table_prefix."item_data (
                            item_data_id mediumint(10) unsigned NOT NULL auto_increment,
                            item_name varchar(255) NOT NULL,
                            data_row_one varchar(255) NOT NULL,
                            data_row_two varchar(255) NOT NULL default ' ',
                            data_row_three varchar(255) NOT NULL default ' ',
                            data_row_four varchar(255) NOT NULL default ' ',
                            data_row_five varchar(255) NOT NULL default ' ',
                            item_jobs varchar(255) NOT NULL default ' ',
                            item_level tinyint(2) NOT NULL,
                            dropped_from varchar(255) NOT NULL default ' ',
                            added_by varchar(30) NOT NULL,
                            updated_by varchar(30) ' ',
                          PRIMARY KEY  (`item_data_id`));",
                         "ALTER TABLE ".$table_prefix."classes ADD class_short_name VARCHAR(3) DEFAULT NULL ;",
                         "UPDATE ".$table_prefix."classes set class_short_name = 'BRD' where class_name = 'Bard';",
                         "UPDATE ".$table_prefix."classes set class_short_name = 'BST' where class_name = 'Beastmaster';",
                         "UPDATE ".$table_prefix."classes set class_short_name = 'BLM' where class_name = 'Black Mage';",
                         "UPDATE ".$table_prefix."classes set class_short_name = 'BLU' where class_name = 'Blue Mage';",
                         "UPDATE ".$table_prefix."classes set class_short_name = 'COR' where class_name = 'Corsair';",
                         "UPDATE ".$table_prefix."classes set class_short_name = 'DNC' where class_name = 'Dancer';",
                         "UPDATE ".$table_prefix."classes set class_short_name = 'DRK' where class_name = 'Dark Knight';",
                         "UPDATE ".$table_prefix."classes set class_short_name = 'DRG' where class_name = 'Dragoon';",
                         "UPDATE ".$table_prefix."classes set class_short_name = 'MNK' where class_name = 'Monk';",
                         "UPDATE ".$table_prefix."classes set class_short_name = 'NIN' where class_name = 'Ninja';",
                         "UPDATE ".$table_prefix."classes set class_short_name = 'PLD' where class_name = 'Paladin';",
                         "UPDATE ".$table_prefix."classes set class_short_name = 'PUP' where class_name = 'Pupppetmaster';",
                         "UPDATE ".$table_prefix."classes set class_short_name = 'RNG' where class_name = 'Ranger';",
                         "UPDATE ".$table_prefix."classes set class_short_name = 'RDM' where class_name = 'Red Mage';",
                         "UPDATE ".$table_prefix."classes set class_short_name = 'SAM' where class_name = 'Samurai';",
                         "UPDATE ".$table_prefix."classes set class_short_name = 'SCH' where class_name = 'Scholar';",
                         "UPDATE ".$table_prefix."classes set class_short_name = 'SMN' where class_name = 'Summoner';",
                         "UPDATE ".$table_prefix."classes set class_short_name = 'THF' where class_name = 'Thief';",
                         "UPDATE ".$table_prefix."classes set class_short_name = 'UNK' where class_name = 'Unknown';",
                         "UPDATE ".$table_prefix."classes set class_short_name = 'WAR' where class_name = 'Warrior';",
                         "UPDATE ".$table_prefix."classes set class_short_name = 'WHM' where class_name = 'White Mage';"                         
        );

        foreach ($queries as $sql)
        {
            $db->query($sql);
        }

        // Process adding/updating the version string in the config table
        $sql = "SELECT config_value 
                FROM ".$table_prefix."config
                WHERE config_name = 'ffdkp_version'";
        $version = $db->query_first($sql);
        if (isset($version))
        {
            $sql = "UPDATE ".$table_prefix."config
                    SET config_value = '0.1.4'
                    WHERE config_name = 'ffdkp_version'";
        }
        else
        {
            $sql = "INSERT INTO ".$table_prefix."config
                    (config_name, config_value)
                    VALUES ('ffdkp_version', '0.1.4')";
        }
        $db->query($sql);
        
        $this->finalize($index);
    }
    
    function upgrade_e100($index)
    {
        global $db, $eqdkp, $user, $tpl, $pm;
        global $SID, $table_prefix;

        $queries = array(
            "CREATE TABLE IF NOT EXISTS " . $table_prefix . "member_flags (
               flag_id smallint(3) unsigned NOT NULL UNIQUE,
               flag_name varchar(50) NOT NULL));",
            "INSERT INTO " . $table_prefix . "member_flags (flag_id, flag_name) VALUES ('0', '');",
            "INSERT INTO " . $table_prefix . "member_flags (flag_id, flag_name) VALUES ('1', 'Member');",
            "ALTER TABLE " . $table_prefix . "members ADD member_flag smallint(3) NOT NULL default '0' AFTER member_class;",
            "INSERT INTO " . $table_prefix . "config (config_name, config_value) VALUES ('parsetags', '');",
            "INSERT INTO " . $table_prefix . "styles (style_id, style_name, template_path, body_background, body_link, body_link_style, body_hlink, body_hlink_style, header_link, header_link_style, header_hlink, header_hlink_style, tr_color1, tr_color2, th_color1, fontface1, fontface2, fontface3, fontsize1, fontsize2, fontsize3, fontcolor1, fontcolor2, fontcolor3, fontcolor_neg, fontcolor_pos, table_border_width, table_border_color, table_border_style, input_color, input_border_width, input_border_color, input_border_style) VALUES (10, 'dkpUA', 'default', '253546', 'C6C6C6', 'underline', '576695', 'underline', 'C6C6C6', 'none', 'C6C6C6', 'underline', '39495A', '283846', '1F2F3D', 'Verdana', 'Verdana', 'Verdana', 10, 11, 12, 'C6C6C6', 'C6C6C6', '000000', 'FF0000', '00C000', 1, '60707E', 'solid', 'FFFFFF', 1, '60707E', 'solid');",
            "INSERT INTO " . $table_prefix . "styles (style_id, style_name, template_path, body_background, body_link, body_link_style, body_hlink, body_hlink_style, header_link, header_link_style, header_hlink, header_hlink_style, tr_color1, tr_color2, th_color1, fontface1, fontface2, fontface3, fontsize1, fontsize2, fontsize3, fontcolor1, fontcolor2, fontcolor3, fontcolor_neg, fontcolor_pos, table_border_width, table_border_color, table_border_style, input_color, input_border_width, input_border_color, input_border_style) VALUES (11, 'subSilver', 'default', 'FFFFFF', '006699', 'underline', 'DD6900', 'underline', 'FFA34F', 'none', 'FFA34F', 'underline', 'DEE3E7', 'EFEFEF', '1073A5', 'Verdana, Arial', 'Verdana, Arial', 'Verdana, Arial', 10, 11, 12, '000000', '000000', '000000', 'F80000', '008800', 1, '006699', 'solid', 'FFFFFF', 1, '000000', 'solid');",
            "INSERT INTO " . $table_prefix . "styles (style_id, style_name, template_path, body_background, body_link, body_link_style, body_hlink, body_hlink_style, header_link, header_link_style, header_hlink, header_hlink_style, tr_color1, tr_color2, th_color1, fontface1, fontface2, fontface3, fontsize1, fontsize2, fontsize3, fontcolor1, fontcolor2, fontcolor3, fontcolor_neg, fontcolor_pos, table_border_width, table_border_color, table_border_style, input_color, input_border_width, input_border_color, input_border_style) VALUES (12, 'EQdkp VB2', 'default', 'FFFFFF', '000000', 'underline', 'FF4400', 'underline', 'FFF788', 'none', 'FFF788', 'underline', 'F1F1F1', 'DFDFDF', '8080A6', 'Verdana, Arial', 'Verdana, Arial', 'Verdana, Arial', 10, 11, 12, '000000', '000000', '000000', 'F80000', '008800', 1, '555576', 'solid', 'FFFFFF', 1, '000000', 'solid');",
            "INSERT INTO " . $table_prefix . "styles (style_id, style_name, template_path, body_background, body_link, body_link_style, body_hlink, body_hlink_style, header_link, header_link_style, header_hlink, header_hlink_style, tr_color1, tr_color2, th_color1, fontface1, fontface2, fontface3, fontsize1, fontsize2, fontsize3, fontcolor1, fontcolor2, fontcolor3, fontcolor_neg, fontcolor_pos, table_border_width, table_border_color, table_border_style, input_color, input_border_width, input_border_color, input_border_style) VALUES (13, 'EQCPS', 'default', '7B7984', '151F41', 'underline', '800000', 'underline', 'FFFFFF', 'none', 'FFFFFF', 'none', 'CECBCE', 'BDBABD', '424952', 'Verdana, Arial', 'Verdana, Arial', 'Verdana, Arial', 10, 11, 12, '000000', '000000', '000000', '800000', '008000', 1, '000000', 'solid', 'C0C0C0', 1, '000000', 'solid');",
            "INSERT INTO " . $table_prefix . "style_config (style_id, attendees_columns, logo_path) VALUES (10, '8', 'dkpua_logo.gif');",
            "INSERT INTO " . $table_prefix . "style_config (style_id, attendees_columns, logo_path) VALUES (11, '8', 'subsilver_logo.gif');",
            "INSERT INTO " . $table_prefix . "style_config (style_id, attendees_columns, logo_path) VALUES (12, '8', 'logo.gif');",
            "INSERT INTO " . $table_prefix . "style_config (style_id, attendees_columns, logo_path) VALUES (13, '8', 'logo.gif');"
        );

        foreach ( $queries as $sql )
        {
            $db->query($sql);
        }

        $this->finalize($index);
    }

    function display_form()
    {
        global $db, $eqdkp, $user, $tpl, $pm;
        global $SID;

        //
        // Attempt to pull the current version out of the database
        //
        $sql = "SELECT config_value as version
                FROM ".CONFIG_TABLE."
                WHERE config_name = 'ffdkp_version'";

        $version_info = $db->query_first($sql);
        if (isset($version_info))
        {
            $tpl->assign_vars(array(
                'S_SELECT_VERSION'   => false,
                'VERSION'            => $version_info,
                'L_EQDKP_UPGRADE'    => $user->lang['eqdkp_upgrade'],
                'L_DETECTED_VERSION' => $user->lang['detected_version']." $version_info",
                'L_DETECTED_BODY'    => $user->lang['detected_body'],
                'L_UPGRADE'          => $user->lang['upgrade'])
            );
        }
        else
        {
            foreach ( $this->versions as $version )
            {
                // This will never happen if common.php's been upgraded already; I'm a re-re!
                $selected = ( $version == EQDKP_VERSION ) ? ' selected="selected"' : '';

                $tpl->assign_block_vars('version_row', array(
                    'VALUE'    => str_replace('.', '', $version),
                    'SELECTED' => $selected,
                    'OPTION'   => 'FFdkp ' . $version)
                );
            }

            $tpl->assign_vars(array(
                'S_SELECT_VERSION' => true,
                'L_EQDKP_UPGRADE'  => $user->lang['eqdkp_upgrade'],
                'L_SELECT_VERSION' => $user->lang['select_version'],
                'L_UPGRADE'        => $user->lang['upgrade'])
            );
        }
        
        $eqdkp->set_vars(array(
            'page_title'    => $user->lang['eqdkp_upgrade'],
            'template_file' => 'upgrade.html',
            'display'       => true)
        );
    }
}

$upgrade = new Upgrade();

// And the upgrade-o-matic 5000 takes care of the rest.
?>
